﻿package com.member.model;

import java.io.*;

import java.sql.*;
import java.util.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.exchangecash.model.ExchangeCashVO;
import com.topupdetail.model.TopUpDetailVO;
import com.violationlist.model.ViolationListVO;
import com.carorderdetail.model.CarOrderDetailVO;
import com.defaultlist.*;
import com.defaultlist.model.DefaultCondListDAO;
import com.defaultlist.model.DefaultCondListVO;

public class MemberDAO implements MemberDAO_interface {
	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CARPOOL");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	// 1. 新增會員
	private static final String INSERT_MEMBER1 = "INSERT INTO member(member_id, account, password, name, nickname, sex, national_id, "
			+ "birthday, address, phone, mobile, reg_date, status, balance, d_evaluation, "
			+ "r_evaluation, veri_code, picture) VALUES(member_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, 10, 0, 0, 0, ?, ?)";
	// 1. 新增會員
	private static final String INSERT_MEMBER2 = "INSERT INTO member(member_id, account, password, name, nickname, sex, national_id, "
			+ "birthday, address, phone, mobile, reg_date, status, balance, d_evaluation, "
			+ "r_evaluation, veri_code) VALUES(member_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE, 10, 0, 0, 0, ?)";

	// 3. 唯一性驗證 - 帳號
	private static final String CHECK_ACCOUNT = "SELECT count(*) FROM member WHERE account=?";
	// 3. 唯一性驗證 - 暱稱
	private static final String CHECK_NICKNAME = "SELECT count(*) FROM member WHERE nickname=?";
	// 3. 唯一性驗證 - 身分證字號
	private static final String CHECK_NATIONALID = "SELECT count(*) FROM member WHERE national_id=?";
	// 3. 唯一性驗證 - 手機號碼
	private static final String CHECK_MOBILE = "SELECT count(*) FROM member WHERE mobile=?";

	// 變更會員資料 - 暱稱唯一性驗證
	private static final String CHECK_NICKNAME_FOR_UPDATE = "SELECT count(*) FROM member WHERE nickname=? AND member_id<>?";
	// 變更會員資料 - 手機號碼唯一性驗證
	private static final String CHECK_MOBILE_FOR_UPDATE = "SELECT count(*) FROM member WHERE mobile=? AND member_id<>?";

	// 變更會員狀態 - 未驗證會員、已驗證會員、停權會員
	private static final String UPDATE_MEMBER_STATUS = "UPDATE member SET status=? WHERE member_id=?";
	// 變更會員資料 - 基本資料(可變更的欄位為：姓名、暱稱、生日、住址、住家號碼)
	private static final String UPDATE_MEMBER_DATA = "UPDATE member SET name=?, nickname=?, birthday=?, address=?, phone=?, mobile=? WHERE member_id=?";
	// 變更會員資料 - 照片
	private static final String UPDATE_PHOTO = "UPDATE member SET picture=? WHERE member_id=?";
	// 變更會員資料 - 密碼
	private static final String UPDATE_PASSWORD = "UPDATE member SET password=? WHERE member_id=?";

	// 顯示會員資料-供其他會員查詢、或會員本身修改
	private static final String QUERY_MEMBER_DATA_FOR_UPDATE = "SELECT member_id, account, password, name, nickname, sex, national_id, birthday, address, phone, mobile,"
			+ "status, balance, d_evaluation, r_evaluation, susp_start_date, susp_end_date, veri_code FROM member "
			+ "WHERE member_id=?";
	// 顯示所有會員的資料
	private static final String QUERY_ALL_MEMBER = "SELECT * FROM member ORDER BY member_id desc";
	// 取得會員的所有共乘條件
	private static final String GET_ALL_DEFAULTCONDLIST_FROM_MEMBER = "SELECT * FROM DEFAULTCONDLIST WHERE member_id=?";
	// 變更餘額-折現、儲值、交易
	private static final String UPDATE_BALANCE = "UPDATE member SET balance=? WHERE member_id=?";
	// 取得會員所有折現記錄
	private static final String GET_ALL_EXCH_BY_MEMID = "SELECT * FROM exchangecash WHERE member_id=? AND status=?";
	// 取得會員所有儲值記錄 - 未完成 已完成
	private static final String GET_ALL_TOP_BY_MEMID = "SELECT * FROM topupdetail WHERE member_id=? AND pay_by=? AND status=?";
	// 取得會員所有儲值記錄
	private static final String GET_ALL_TOP = "SELECT * FROM topupdetail WHERE member_id=? AND pay_by=?";
	// 取得會員編號
	private static final String GET_MEMID = "SELECT member_id FROM member WHERE account=?";

	private static final String GET_ALLMEMBER_STMT = "SELECT account,password  FROM member";
	// 變更停權起訖日期
	private static final String UPDATE_SUSP = "UPDATE member SET susp_start_date=SYSDATE, susp_end_date=SYSDATE+? WHERE member_id=?";
	// 停權結束
	private static final String SUSP_END = "UPDATE member SET susp_start_date=null, susp_end_date=null WHERE member_id=?";
	// 顯示被停權的所有會員
	private static final String GET_ALL_BY_STATUS = "SELECT member_id, susp_end_date FROM member WHERE status=? ORDER BY susp_end_date";
	// 顯示會員所有交易的資料
	private static final String GET_ALL_ORDERDETAIL = "SELECT * FROM car_order_detail WHERE member_id=? AND status=?";
	// 會員查詢自己的違規記錄
	private static final String GET_ALL_VIOL = "SELECT * FROM violationlist WHERE member_id=?";
	// 取得所有超過一天且未完成的交易
	private static final String GET_ALL_ORDERDETAIL_STATUS = "SELECT * FROM car_order_detail WHERE status=? AND SYSDATE>close_time+1 ORDER BY close_time";
	// 取得會員目前所有的違規次數
	private static final String GET_TIMES_VIOL = "select count(*) from violationlist where member_id=? group by member_id";
	//登入時尋找單一帳號比對
	public static final String QUERY_MEMBER_DATA_FOR_ACCOUNT = "SELECT member_id, account, password, name, nickname, sex, national_id, birthday, address, phone, mobile,status, balance, d_evaluation, r_evaluation, susp_start_date, susp_end_date, veri_code" +
				" FROM member WHERE account=?";
	// 變更會員資料(忘記密碼可變更的欄位為：密碼)
	public static final String UPDATE_MEMBER_PASSWORD = "UPDATE member SET password=?, name=? WHERE account=? and mobile=?";	

	// 取得乘客的最新評價分數
	private static final String GET_NEW_RIDER_SCORE = "SELECT rider_scores FROM car_order_detail WHERE member_id=? AND close_time=(SELECT max(close_time) FROM"
			+ " car_order_detail WHERE member_id=member_id)";
	// 取得駕駛的最新平均評價分數
	private static final String GET_NEW_DRIVER_SCORE = "SELECT avg(driver_scores) FROM car_order_detail WHERE close_time="
			+ "(SELECT max(close_time) FROM car_order_detail) AND order_id=(SELECT order_id FROM car_order WHERE close_time=(SELECT max(close_time) FROM car_order WHERE member_id=?))";
	// 取得乘客歷史評價記錄
	private static final String GET_RIDER_SCORE = "select rider_scores, close_time from car_order_detail where member_id=? order by close_time desc";
	// 取得駕駛歷史評價記錄
	private static final String GET_DRIVER_SCORE = "select avg(driver_scores) from car_order_detail where order_id=?";
	private static final String GET_ORDER_ID = "select order_id, close_time from car_order where member_id=?";

	// 1. 設定會員資料
	@Override
	public int insertMember(MemberVO memberVO, java.io.File photo,
			List<DefaultCondListVO> list) {
		int updateCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();

			String col[] = { "member_id" };
			conn.setAutoCommit(false);

			if (photo != null) {
				pstmt = conn.prepareStatement(INSERT_MEMBER1, col);
				long flen = photo.length();
				InputStream fin = new FileInputStream(photo);
				pstmt.setBinaryStream(12, fin, (int) flen);
			} else if (photo == null) {
				pstmt = conn.prepareStatement(INSERT_MEMBER2, col);
			}

			pstmt.setString(1, memberVO.getAccount());
			pstmt.setString(2, memberVO.getPassword());
			pstmt.setString(3, memberVO.getName());
			pstmt.setString(4, memberVO.getNickname());
			pstmt.setString(5, memberVO.getSex());
			pstmt.setString(6, memberVO.getNational_id());
			pstmt.setDate(7, memberVO.getBirthday());
			pstmt.setString(8, memberVO.getAddress());
			pstmt.setString(9, memberVO.getPhone());
			pstmt.setString(10, memberVO.getMobile());
			pstmt.setString(11, memberVO.getVeri_code());

			updateCount = pstmt.executeUpdate();

			String next_memberId = null;
			ResultSet rs = pstmt.getGeneratedKeys();
			if (rs.next())
				next_memberId = rs.getString(1);
			rs.close();

			DefaultCondListDAO dao = new DefaultCondListDAO();
			for (DefaultCondListVO aDclVO : list) {
				aDclVO.setMember_id(next_memberId);
				dao.insertDefaultCond(aDclVO, conn);
			}

			conn.commit();

			conn.setAutoCommit(true);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return updateCount;
	}

	// 3. 唯一性驗證 - 帳號，暱稱，身分證字號，手機號碼
	public int checkOnlyCond(String account, String nickname,
			String national_id, String mobile) {
		int queryCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			if (account != null) {
				pstmt = conn.prepareStatement(CHECK_ACCOUNT);
				pstmt.setString(1, account);
			}
			if (nickname != null) {
				pstmt = conn.prepareStatement(CHECK_NICKNAME);
				pstmt.setString(1, nickname);
			}
			if (national_id != null) {
				pstmt = conn.prepareStatement(CHECK_NATIONALID);
				pstmt.setString(1, national_id);
			}
			if (mobile != null) {
				pstmt = conn.prepareStatement(CHECK_MOBILE);
				pstmt.setString(1, mobile);
			}

			rs = pstmt.executeQuery();
			while (rs.next()) {
				queryCount = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {

				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {

				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {

				}
			}
		}
		return queryCount;
	}

	// 3. 產生驗證碼
	// @Override
	// public String getVeriCode() {
	// String veriCode = null;
	// String[] value = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
	// "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
	// "W", "X", "Y", "Z", "0", "1", "2", "3", "4", "5", "6", "7",
	// "8", "9" };
	// StringBuilder sb = new StringBuilder();
	// for (int i = 0; i < 10; i++) {
	// sb.append(value[(int) (Math.random() * 36)]);
	// }
	// veriCode = sb.toString();
	// return veriCode;
	// }

	// 6. 驗證正確
	@Override
	public boolean equalVeriCode(String veriCode, String member_id) {
		boolean check = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(QUERY_MEMBER_DATA_FOR_UPDATE);
			conn.setAutoCommit(false);
			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String veri_code = rs.getString("veri_code");
				if (veriCode.equals(veri_code)) {
					check = true;
					updateStatus(11, member_id, conn);
				}
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return check;
	}

	// 7. 變更會員狀態，當驗證正確後呼叫此方法並傳狀態及會員編號
	@Override
	public int updateStatus(Integer status, String member_id, Connection conn) {
		int updateCount = 0;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_MEMBER_STATUS);

			pstmt.setInt(1, status);
			pstmt.setString(2, member_id);

			updateCount = pstmt.executeUpdate();
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return updateCount;
	}

	// 1. 儲值-變更餘額
	@Override
	public void updateBalance(MemberVO memberVO, TopUpDetailVO topUpDetailVO,
			Integer fee, Connection conn) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_BALANCE);

			int cash = 0;
			if (topUpDetailVO != null) {
				cash = memberVO.getBalance() + topUpDetailVO.getDeposit();
				pstmt.setInt(1, cash);
				pstmt.setString(2, topUpDetailVO.getMember_id());
			} else {
				cash = memberVO.getBalance() + fee;
				pstmt.setInt(1, cash);
				pstmt.setString(2, memberVO.getMember_id());
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	// 1. 顯示該會員資料
	@Override
	public MemberVO findByMemberId(String member_id) {
		MemberVO memberVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(QUERY_MEMBER_DATA_FOR_UPDATE);

			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				memberVO = new MemberVO();
				memberVO.setMember_id(rs.getString("member_id"));
				memberVO.setAccount(rs.getString("account"));
				memberVO.setPassword(rs.getString("password"));
				memberVO.setName(rs.getString("name"));
				memberVO.setNickname(rs.getString("nickname"));
				memberVO.setSex(rs.getString("sex"));
				memberVO.setNational_id(rs.getString("national_id"));
				memberVO.setBirthday(rs.getDate("birthday"));
				memberVO.setAddress(rs.getString("address"));
				memberVO.setPhone(rs.getString("phone"));
				memberVO.setMobile(rs.getString("mobile"));
				memberVO.setStatus(rs.getInt("status"));
				memberVO.setBalance(rs.getInt("balance"));
				memberVO.setSusp_start_date(rs.getTimestamp("susp_start_date"));
				memberVO.setSusp_end_date(rs.getTimestamp("susp_end_date"));
				memberVO.setVeri_code(rs.getString("veri_code"));
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
			// } catch (IOException e) {
			// e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return memberVO;
	}

	// 變更會員資料 - 密碼
	public int updatePassword(String password, String member_id) {
		int updateCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();

			pstmt = conn.prepareStatement(UPDATE_PASSWORD);
			conn.setAutoCommit(false);

			pstmt.setString(1, password);
			pstmt.setString(2, member_id);

			updateCount = pstmt.executeUpdate();
			conn.commit();

			conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return updateCount;
	}

	// 變更會員資料 - 基本資料
	@Override
	public int updateMemberData(MemberVO memberVO) {
		int updateCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(UPDATE_MEMBER_DATA);
			// conn.setAutoCommit(false);

			pstmt.setString(1, memberVO.getName());
			pstmt.setString(2, memberVO.getNickname());
			pstmt.setDate(3, memberVO.getBirthday());
			pstmt.setString(4, memberVO.getAddress());
			pstmt.setString(5, memberVO.getPhone());
			pstmt.setString(6, memberVO.getMobile());
			pstmt.setString(7, memberVO.getMember_id());

			// conn.commit();
			updateCount = pstmt.executeUpdate();
			// conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return updateCount;
	}

	// 查詢所有會員
	public List<MemberVO> getAll() {
		List<MemberVO> list = new ArrayList<MemberVO>();
		MemberVO memberVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(QUERY_ALL_MEMBER);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				memberVO = new MemberVO();
				memberVO.setMember_id(rs.getString("member_id"));
				memberVO.setAccount(rs.getString("account"));
				memberVO.setPassword(rs.getString("password"));
				memberVO.setName(rs.getString("name"));
				memberVO.setNickname(rs.getString("nickname"));
				memberVO.setSex(rs.getString("sex"));
				memberVO.setNational_id(rs.getString("national_id"));
				memberVO.setBirthday(rs.getDate("birthday"));
				memberVO.setAddress(rs.getString("address"));
				memberVO.setPhone(rs.getString("phone"));
				memberVO.setMobile(rs.getString("mobile"));
				memberVO.setBalance(rs.getInt("balance"));
				memberVO.setReg_date(rs.getDate("reg_date"));
				list.add(memberVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 變更餘額 - 折現、扣除分攤費用
	@Override
	public void updateBalance(MemberVO memberVO, ExchangeCashVO exchangeCashVO,
			CarOrderDetailVO carOrderDetailVO, Connection conn) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_BALANCE);
			int cash = 0;
			if (exchangeCashVO != null) {
				cash = memberVO.getBalance() - exchangeCashVO.getMoney();
				pstmt.setInt(1, cash);
				pstmt.setString(2, exchangeCashVO.getMember_id());
			}
			if (carOrderDetailVO != null) {
				cash = memberVO.getBalance() - carOrderDetailVO.getFee();
				pstmt.setInt(1, cash);
				pstmt.setString(2, memberVO.getMember_id());
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	// 會員查詢折現記錄
	@Override
	public List<ExchangeCashVO> getAllExchByMemId(String member_id,
			Integer status) {
		List<ExchangeCashVO> list = new ArrayList<ExchangeCashVO>();
		ExchangeCashVO ec = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_EXCH_BY_MEMID);

			pstmt.setString(1, member_id);
			pstmt.setInt(2, status);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				ec = new ExchangeCashVO();
				ec.setExchange_id(rs.getString("exchange_id"));
				ec.setMoney(rs.getInt("money"));
				ec.setRemi_acc(rs.getString("remi_acc"));
				ec.setRemi_bank(rs.getString("remi_bank"));
				ec.setBank_code(rs.getString("bank_code"));
				ec.setStatus(rs.getInt("status"));
				ec.setUsername(rs.getString("username"));
				ec.setAppli_date(rs.getDate("appli_date"));
				list.add(ec);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 會員查詢儲值記錄
	@Override
	public List<TopUpDetailVO> getAllTopByMemId(String member_id,
			Integer pay_by, Integer status) {
		List<TopUpDetailVO> list = new ArrayList<TopUpDetailVO>();
		TopUpDetailVO top = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_TOP_BY_MEMID);

			pstmt.setString(1, member_id);
			pstmt.setInt(2, pay_by);
			pstmt.setInt(3, status);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				top = new TopUpDetailVO();
				top.setBack_last3code(rs.getString("back_last3code"));
				top.setCard_date(rs.getString("card_date"));
				top.setCredit_card(rs.getString("credit_card"));
				top.setDeposit(rs.getInt("deposit"));
				// top.setMember_id(rs.getString("member_id"));
				top.setOwner_name(rs.getString("owner_name"));
				top.setOwner_national_id(rs.getString("owner_national_id"));
				top.setRemi_acc(rs.getString("remi_acc"));
				top.setStatus(rs.getInt("status"));
				top.setRemi_time(rs.getString("remi_time"));
				top.setTime(rs.getDate("time"));
				list.add(top);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 透過帳號密碼取得會員編號
	@Override
	public String getMemIdByAcc(String acc) {
		String member_id = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_MEMID);
			pstmt.setString(1, acc);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				member_id = rs.getString("member_id");
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return member_id;
	}

	// 變更會員照片
	@Override
	public void updatePhoto(File photo, String member_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();

			pstmt = conn.prepareStatement(UPDATE_PHOTO);
			conn.setAutoCommit(false);

			long flen = photo.length();
			InputStream fin = new FileInputStream(photo);
			pstmt.setBinaryStream(1, fin, (int) flen);

			pstmt.setString(2, member_id);
			pstmt.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	// 會員變更資料 - 唯一性驗證
	public int onlyCheckForUpdate(String nickname, String mobile,
			String member_id) {
		int queryCount = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			if (nickname != null) {
				pstmt = conn.prepareStatement(CHECK_NICKNAME_FOR_UPDATE);
				pstmt.setString(1, nickname);
			}
			if (mobile != null) {
				pstmt = conn.prepareStatement(CHECK_MOBILE_FOR_UPDATE);
				pstmt.setString(1, mobile);
			}
			pstmt.setString(2, member_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				queryCount = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return queryCount;
	}

	// 取得會員設定的所有共乘條件
	@Override
	public List<DefaultCondListVO> getAll(String member_id) {
		List<DefaultCondListVO> list = new ArrayList<DefaultCondListVO>();
		DefaultCondListVO dcl = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_DEFAULTCONDLIST_FROM_MEMBER);

			pstmt.setString(1, member_id);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				dcl = new DefaultCondListVO();
				dcl.setDefaultcond_id(rs.getInt("defaultcond_id"));
				dcl.setMember_id(rs.getString("member_id"));
				dcl.setValue(rs.getInt("value"));
				list.add(dcl);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// anroid
	public boolean isMemberValid(String account, String password) {
		MemberVO memberVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean pan = false;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALLMEMBER_STMT);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				memberVO = new MemberVO();
				memberVO.setAccount(rs.getString("account"));
				memberVO.setPassword(rs.getString("password"));

				if (account.equals(memberVO.getAccount())
						&& password.equals(memberVO.getPassword())) {
					findByPrimaryKey(account);
					pan = true;
				}

			}

		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return pan;
	}

	public List<MemberVO> findByPrimaryKey(String account) {
		List<MemberVO> list = new ArrayList<MemberVO>();
		MemberVO memberVO = null;
		Connection conn = null;

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(QUERY_MEMBER_DATA_FOR_UPDATE);

			pstmt.setString(1, account);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				memberVO = new MemberVO();
				memberVO.setMember_id(rs.getString("member_id"));
				memberVO.setName(rs.getString("name"));
				memberVO.setNickname(rs.getString("nickname"));
				memberVO.setSex(rs.getString("sex"));
				memberVO.setNational_id(rs.getString("national_id"));
				memberVO.setBirthday(rs.getDate("birthday"));
				memberVO.setAddress(rs.getString("address"));
				memberVO.setPhone(rs.getString("phone"));
				list.add(memberVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}

		return list;
	}

	// 停權時，顯示起訖日期
	public void updateSusp(Integer days, String member_id, Connection conn) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(UPDATE_SUSP);
			pstmt.setInt(1, days);
			pstmt.setString(2, member_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	// 停權結束 - 變更停權起訖日期
	public void updateSusp(String member_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();

			pstmt = conn.prepareStatement(SUSP_END);
			conn.setAutoCommit(false);
			pstmt.setString(1, member_id);
			pstmt.executeUpdate();

			updateStatus(11, member_id, conn);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException excep) {
					throw new RuntimeException("rollback error occured. "
							+ excep.getMessage());
				}
			}
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
	}

	// 顯示所有被停權的會員
	public List<MemberVO> getAllByStatus(Integer status) {
		List<MemberVO> list = new ArrayList<MemberVO>();
		MemberVO memVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();

			pstmt = conn.prepareStatement(GET_ALL_BY_STATUS);
			pstmt.setInt(1, status);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				memVO = new MemberVO();
				memVO.setMember_id(rs.getString("member_id"));
				memVO.setSusp_end_date(rs.getTimestamp("susp_end_date"));
				list.add(memVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 取得會員所有交易資料 - 未完成和已完成
	public List<CarOrderDetailVO> getAllOrderDetail(String member_id,
			String status) {
		List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
		CarOrderDetailVO codVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_ORDERDETAIL);
			pstmt.setString(1, member_id);
			pstmt.setString(2, status);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				codVO = new CarOrderDetailVO();
				codVO.setCarPoolType(rs.getString("car_pool_type"));
				codVO.setCloseTime(rs.getTimestamp("close_time"));
				codVO.setCreateTime(rs.getTimestamp("create_time"));
				codVO.setFee(rs.getInt("fee"));
				codVO.setOrderId(rs.getString("order_id"));
				codVO.setRiderPathId(rs.getString("rider_path_id"));
				list.add(codVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 會員查詢自己的違規記錄
	@Override
	public List<ViolationListVO> getAllViolByMem(String member_id) {
		List<ViolationListVO> list = new ArrayList<ViolationListVO>();
		ViolationListVO vlVO = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_VIOL);
			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vlVO = new ViolationListVO();
				vlVO.setViolation_id(rs.getInt("violation_id"));
				vlVO.setViol_date(rs.getTimestamp("viol_date"));
				vlVO.setStatus(rs.getInt("status"));
				list.add(vlVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 取得會員所有的儲值記錄 - ATM 信用卡
	@Override
	public List<TopUpDetailVO> getAllTopByMem(String member_id, Integer pay_by) {
		List<TopUpDetailVO> list = new ArrayList<TopUpDetailVO>();
		TopUpDetailVO top = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_TOP);

			pstmt.setString(1, member_id);
			pstmt.setInt(2, pay_by);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				top = new TopUpDetailVO();
				top.setBack_last3code(rs.getString("back_last3code"));
				top.setCard_date(rs.getString("card_date"));
				top.setCredit_card(rs.getString("credit_card"));
				top.setDeposit(rs.getInt("deposit"));
				// top.setMember_id(rs.getString("member_id"));
				top.setOwner_name(rs.getString("owner_name"));
				top.setOwner_national_id(rs.getString("owner_national_id"));
				top.setRemi_acc(rs.getString("remi_acc"));
				top.setStatus(rs.getInt("status"));
				top.setRemi_time(rs.getString("remi_time"));
				top.setTime(rs.getDate("time"));
				list.add(top);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	@Override
	public List<CarOrderDetailVO> getAllTransByStatus(String status) {
		List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
		CarOrderDetailVO codVO = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ALL_ORDERDETAIL_STATUS);

			pstmt.setString(1, status);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				codVO = new CarOrderDetailVO();
				codVO.setCloseTime(rs.getTimestamp("close_time"));
				codVO.setCreateTime(rs.getTimestamp("create_time"));
				codVO.setFee(rs.getInt("fee"));
				codVO.setMemberId(rs.getString("member_id"));
				codVO.setOrderId(rs.getString("order_id"));
				codVO.setStatus(rs.getString("status"));
				list.add(codVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	@Override
	public int getTimesForViol(String member_id) {
		int count = 0;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_TIMES_VIOL);

			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return count;
	}

	// 取得乘客的最新評價分數
	@Override
	public int getScoreFromRider(String member_id) {
		int score = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_NEW_RIDER_SCORE);
			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				score = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return score;
	}

	// 取得駕駛的最新平均評價分數
	@Override
	public int getScoreFromDriver(String member_id) {
		int score = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_NEW_DRIVER_SCORE);
			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				score = rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return score;
	}

	// 取得乘客歷史評價記錄
	@Override
	public List<CarOrderDetailVO> getScoreFromRiderForHistroy(String member_id) {
		List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
		CarOrderDetailVO codVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_RIDER_SCORE);
			pstmt.setString(1, member_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				codVO = new CarOrderDetailVO();
				codVO.setCloseTime(rs.getTimestamp("close_time"));
				codVO.setRider_scores(rs.getInt("rider_scores"));
				list.add(codVO);
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 取得駕駛歷史評價記錄
	@Override
	public List<ScoreFromDriverVO> getScoreFromDriverForHistroy(String member_id) {
		List<ScoreFromDriverVO> list = new ArrayList<ScoreFromDriverVO>();
		ScoreFromDriverVO sfdVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(GET_ORDER_ID);
			pstmt.setString(1, member_id);
			rs1 = pstmt.executeQuery();
			while (rs1.next()) {
				if (getAnyOrderDetail(rs1.getString("order_id"))) {
					sfdVO = new ScoreFromDriverVO();
					sfdVO.setClose_time(rs1.getTimestamp("close_time"));
					pstmt = conn.prepareStatement(GET_DRIVER_SCORE);
					pstmt.setString(1, rs1.getString("order_id"));
					rs2 = pstmt.executeQuery();
					while (rs2.next()) {
						sfdVO.setDriver_scores(rs2.getInt(1));
						list.add(sfdVO);
					}
				}
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs1 != null) {
				try {
					rs1.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (rs2 != null) {
				try {
					rs2.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}

	// 判斷訂單主檔下是否有訂單明細
	public boolean getAnyOrderDetail(String order_id) {
		boolean haveAnyOrderDetail = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			pstmt = conn
					.prepareStatement("select count(*) from car_order_detail where order_id=?");
			pstmt.setString(1, order_id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				if (rs.getInt(1) > 0)
					haveAnyOrderDetail = true;
			}
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return haveAnyOrderDetail;
	}
	@Override
	public MemberVO getMemberId(String account) {
		MemberVO memberVO = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = ds.getConnection();
			pstmt = conn.prepareStatement(QUERY_MEMBER_DATA_FOR_ACCOUNT);
			pstmt.setString(1, account);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				memberVO = new MemberVO();
				memberVO.setMember_id(rs.getString("member_id"));
				memberVO.setAccount(rs.getString("account"));
				memberVO.setPassword(rs.getString("password"));
				memberVO.setName(rs.getString("name"));
				memberVO.setNickname(rs.getString("nickname"));
				memberVO.setSex(rs.getString("sex"));
				memberVO.setNational_id(rs.getString("national_id"));
				memberVO.setBirthday(rs.getDate("birthday"));
				memberVO.setAddress(rs.getString("address"));
				memberVO.setPhone(rs.getString("phone"));
				memberVO.setMobile(rs.getString("mobile"));
				memberVO.setStatus(rs.getInt("status"));
				memberVO.setBalance(rs.getInt("balance"));
				memberVO.setR_evaluation(rs.getInt("r_evaluation"));
				memberVO.setD_evaluation(rs.getInt("d_evaluation"));
				memberVO.setSusp_start_date(rs.getTimestamp("susp_start_date"));
				memberVO.setSusp_end_date(rs.getTimestamp("susp_end_date"));
				memberVO.setVeri_code(rs.getString("veri_code"));
				// Store the row in the vector
			}

			// Handle any SQL errors
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return memberVO;
	}
	@Override
	public void updateMemberPassword(MemberVO memberVO) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(UPDATE_MEMBER_PASSWORD);
			// conn.setAutoCommit(false);

			pstmt.setString(1, memberVO.getPassword());
			pstmt.setString(2, memberVO.getName());
			pstmt.setString(3, memberVO.getAccount());
			pstmt.setString(4, memberVO.getMobile());
			// conn.commit();
			pstmt.executeUpdate();
			// conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new RuntimeException("A database error occured. "
					+ e.getMessage());
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return;
	}
}
